﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Giaiphongmatbang.DataModel;

namespace Giaiphongmatbang.BLL
{
    public class VillagesRepostory
    {
        private QLGPMBEntities data = new QLGPMBEntities();

        public IEnumerable<Village> GetAll(int pageSize, int pageNumber, ref int totalRow)
        {
            var results = from p in data.Villages
                          select p;
            totalRow = results.Count();
            return results.OrderBy(p=>p.VillageName).Skip((pageNumber - 1) * pageSize).Take(pageSize);
        }

        //Tổng số hộ trên địa bàn xã chịu ảnh hưởng của tất cả các dự án
        public IEnumerable<HouseHold> GetTotalHouseHold(int villageID, int pageSize, int pageNumber, ref int totalRow)
        {
            var result = (from h in data.HouseHolds
                          join i in data.Inventories on h.CMND equals i.CMND
                          
                          where h.Village.VillageID == villageID
                          select h).Distinct();
            totalRow = result.Count();
            return result.OrderByDescending(p => p.FullName).Skip((pageNumber - 1) * pageSize).Take(pageSize);
        }

        //Tổng số hộ trên địa bàn xã chịu ảnh hưởng của 1 dự án
        public IEnumerable<HouseHold> GetTotalHouseHold(int villageID,int projetcID, int pageSize, int pageNumber, ref int totalRow)
        {
            var result = (from h in data.HouseHolds
                          join i in data.Inventories on h.CMND equals i.CMND
                          join p in data.Projects on i.ProjectID equals p.ProjectID
                          where h.Village.VillageID == villageID && p.ProjectID == projetcID
                          select h).Distinct();
            totalRow = result.Count();
            return result.OrderByDescending(p => p.FullName).Skip((pageNumber - 1) * pageSize).Take(pageSize);
        }

        //đếm số dự án trong 1 xã
        public int CountProjectInVillage(int villageID)
        {
            var result = (from p in data.Projects
                          join i in data.Inventories on p.ProjectID equals i.ProjectID
                          join h in data.HouseHolds on i.CMND equals h.CMND
                          where h.Village.VillageID == villageID
                          select p).Distinct();

            int totalRow = result.Count();
            return totalRow;
        }


        //public IEnumerable<Village> GetVillagesAddCountProject()
        //{
        //    var result = from h in data.HouseHolds
        //                 join i
        //}


        //Những dự án có trong địa bàn xã, thị trấn
        //Bên ProjectsReponsitory   

        //Thống kê xã theo dự án
        //public IEnumerable<Village> GetByProjectID()
        //{

        //}

        public District GetDistrictBYVillage(int villageID)
        {
            var result = from p in data.Districts
                         join v in data.Villages on p.DistrictID equals v.District.DistrictID
                         where v.VillageID == villageID
                         select p;
            return result.FirstOrDefault();
        }

        public Village GetByID(int ID)
        {
            var result = from p in data.Villages
                         where p.VillageID == ID
                         select p;
            return result.FirstOrDefault();
        }

        public IEnumerable<Village> GetByDistrictID(int ID)
        {
            var results = from p in data.Villages
                         where p.District.DistrictID == ID
                         select p;
            return results;
        }

        public Village GetByHouseHoldID(int ID)
        {
            return data.Villages.FirstOrDefault(p => p.HouseHolds.Any(q => q.HouseHoldID == ID));
        }
        public void Insert(Village v)
        {
            data.AddToVillages(v);
        }

        public void DeleteByID(int id)
        {
            var obj = GetByID(id);
            if (obj != null)
            {
                data.DeleteObject(obj);
            }
        }

        public void Save()
        {
            data.SaveChanges();
        }
    }
}